package cn.cqsw.dao;

import cn.cqsw.pojo.Employee;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmployeeDao {
    Connection conn=null;
    PreparedStatement ps=null;
    ResultSet rs=null;
    //查询所有
    public List<Employee> findEmployees(){
        List<Employee> employees=new ArrayList<Employee>();
        String sql="select * from tb_employee";
        try {
            conn = BaseDao.getConn();
             ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Employee employee=new Employee();
                employee.setId(rs.getInt("id"));
                employee.setName(rs.getString("name"));
                employee.setBirthday(rs.getString("birthday"));
                employee.setGender(rs.getString("gender"));
                employee.setJobs(rs.getString("jobs"));
                employee.setPhone(rs.getString("phone"));
                employees.add(employee);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            try {
                BaseDao.close(rs,ps,conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return employees;
    }
    //添加员工信息
    public int insertEmployee(Employee employee){
        int result=-1;
        String sql="insert into tb_employee(`name`,birthday,gender,jobs,phone)values(?,?,?,?,?)";
        try {
            conn=BaseDao.getConn();
            ps=conn.prepareStatement(sql);
            ps.setString(1,employee.getName());
            ps.setString(2,employee.getBirthday());
            ps.setString(3,employee.getGender());
            ps.setString(4,employee.getJobs());
            ps.setString(5,employee.getPhone());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            try {
                BaseDao.close(rs,ps,conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }
    //删除员工信息
    public int deleteUserById(int id){
        int result=-1;
        String sql="delete from tb_employee where id = ?";
        try {
            conn=BaseDao.getConn();
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            try {
                BaseDao.close(rs,ps,conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }
    //通过id查询员工信息
    public Employee findEmployeeById(int id){
        Employee employee=null;
        String sql="select * from tb_employee where id = ?";
        try {
            conn=BaseDao.getConn();
            ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                employee=new Employee();
                employee.setId(rs.getInt("id"));
                employee.setName(rs.getString("name"));
                employee.setBirthday(rs.getString("birthday"));
                employee.setGender(rs.getString("gender"));
                employee.setJobs(rs.getString("jobs"));
                employee.setPhone(rs.getString("phone"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            try {
                BaseDao.close(rs,ps,conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return employee;
    }
    //修改员工信息
    public int updateEmployee(Employee employee){
        int result=-1;
        String sql="update tb_employee set `name` =?,birthday=?,gender=?,jobs=?,phone=? where id=?";
        try {
            conn=BaseDao.getConn();
            ps=conn.prepareStatement(sql);
            ps.setString(1,employee.getName());
            ps.setString(2,employee.getBirthday());
            ps.setString(3,employee.getGender());
            ps.setString(4,employee.getJobs());
            ps.setString(5,employee.getPhone());
            ps.setInt(6,employee.getId());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            try {
                BaseDao.close(rs,ps,conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

    public static void main(String[] args) {
        Employee employee=new Employee();
        employee.setId(4);
        employee.setName("张四");
        System.out.println(new EmployeeDao().findEmployeeById(3));
    }
}
